SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by:sfibich
*ScriptName:Get_StatsDuplicates.sql													
*Version:1.0.2013.09.01
*SQL Versions:2008R2
*Purpose:This script shows system statistics that duplicate the first column in a user 
*created statistic.  This normally happens if indexes or statistics are created 
*after a database has been in use for awhile.  This script can show system statistics
*that match the first column of a multiple column user statistics first column, this
*would not be a true duplicate but you may want to be aware of this situation.
*					 
*********************************************************************************************************/
go
select
dups.schema_name 
,dups.object_name
,dups.stat_name
,dups.duplicate_system_stat_name
from
(
	select
	object_schema_name (s1.object_id ) as [schema_name] 
	,o1.name as [object_name]
	,s2.name as [stat_name]
	,s1.name as [duplicate_system_stat_name]
	,count(*) over(partition by sc1.object_id, sc1.stats_id) as [sc1_count]
	,count(*) over(partition by sc2.object_id, sc2.stats_id) as [sc2_count]
	from 
	sys.stats_columns sc1
	inner join
	sys.stats_columns sc2
	on
	sc1.column_id =sc2.column_id 
	and
	sc1.object_id =sc2.object_id 
	and
	sc1.stats_column_id =sc2.stats_column_id 
	inner join
	sys.stats s1
	on
	sc1.object_id =s1.object_id 
	and
	sc1.stats_id =s1.stats_id 
	inner join
	sys.stats s2
	on
	sc2.object_id =s2.object_id 
	and
	sc2.stats_id =s2.stats_id 
	inner join
	sys.objects o1
	on
	o1.object_id=s1.object_id
	where
	sc1.stats_id <> sc2.stats_id 
	and
	s1.auto_created=1
	and
	o1.is_ms_Shipped=0
) dups
where
sc1_count=sc2_count
order by
dups.schema_name 
,dups.object_name
,dups.stat_name